package com.sprucetec.live.common.util;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * Created by mc962 on 16/8/17.
 */
public class ExcelUtil {
    public static void handleExcelResponse(HSSFWorkbook book, HttpServletResponse response, String fileName) {
        OutputStream outputStream = null;

        response.setContentType("application/vnd.ms-excel");
        try {
            response.setHeader("Content-disposition", "attachment;filename="
                    + new String(fileName.getBytes("GBK"), "ISO8859_1") + ".xls");

            outputStream = response.getOutputStream();
            book.write(outputStream);
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        finally {
            if (outputStream != null) {
                try {
                    outputStream.close();
                    outputStream.flush();
//                    book.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static String getExcelStringValue(Row row, Cell cell, int index) {
        cell = row.getCell(index);
        if (cell == null) {
            return null;
        }
        if (cell.getCellTypeEnum().equals(CellType.STRING)) {
            return cell.getStringCellValue();
        }
        if (cell.getCellTypeEnum().equals(CellType.NUMERIC)) {
            DecimalFormat df = new DecimalFormat("0.0000");

            String whatYourWant = df.format(cell.getNumericCellValue());
            return whatYourWant;
        }
        return null;
    }

    public static Date getDateCellValue(Row row, Cell cell, int index) {
        cell = row.getCell(index);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
        sdf.setLenient(false);
        Date maxDate = null;
        try {
            maxDate = sdf.parse("9999/01/01");
        } catch (ParseException e) {
            e.printStackTrace();
        }
        try {
            if(cell != null) {
                if (cell.getCellTypeEnum().equals(CellType.BLANK)) {
                    return null;
                }
                if (cell.getCellTypeEnum().equals(CellType.BOOLEAN)) {
                    return null;
                }
                if (cell.getCellTypeEnum().equals(CellType.ERROR)) {
                    return null;
                }
                if (cell.getCellTypeEnum().equals(CellType.FORMULA)) {
                    Date formulaDate = sdf.parse(cell.getCellFormula()) == null ? null : sdf.parse(cell.getCellFormula());
                    if(formulaDate.after(maxDate)) {
                        return null;
                    }
                    return formulaDate;
                }
                if (cell.getCellTypeEnum().equals(CellType.NUMERIC)) {
                    if(DateUtil.isCellDateFormatted(cell)) {
                        if(DateUtil.getJavaDate(cell.getNumericCellValue()).after(maxDate)) {
                            return null;
                        }
                        return DateUtil.getJavaDate(cell.getNumericCellValue());
                    }else{
                        Double d = cell.getNumericCellValue();
                        Date temDate = DateUtil.getJavaDate(d);
                        if(temDate.after(maxDate)) {
                            return null;
                        }
                        String dateString = sdf.format(temDate);
                        return sdf.parse(dateString);
                    }
                }
                if (cell.getCellTypeEnum().equals(CellType.STRING)) {
                    String cellValue = cell.getStringCellValue();
                    if(cellValue.indexOf("-") > -1) {
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                    }
                    if(StringUtils.isBlank(cellValue)) {
                        return null;
                    }
                    Date date = sdf.parse(cell.getStringCellValue() == null ? cell.getRichStringCellValue().toString() : cell.getStringCellValue());
                    return date;
                }
            }
        }catch (ParseException e) {
            e.printStackTrace();
            return null;
        }
        return null;
    }
}
